﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_ByBackupSetID]
#-- Purpose:		Provides a mechanism to restore a database to a label
#--					via stored procedure call
#--	Last Update:	10/23/2015
#--					For a complete history - please review comments in SVN
#-- Called By:		Restore Database Utility Executable
#--					[dbo].[spRest_ByLabel]
#--					[dbo].[spRest_ByPointInTime]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_ByBackupSetID]
(
	@database_name			sysname,
	@last_backup_set_id		int,
	@restore_now			bit				= 0,
	@verify_only			bit				= 0,
	@destination_db			sysname			= NULL,
	@stopat_time			datetime		= NULL,
	@skip_pre_backup		bit				= 0
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@sError varchar(1000), @sResults varchar(8000), @database sysname, @has_rights bit
DECLARE		@sCommand nvarchar(4000), @name sysname
DECLARE		@progname sysname

--- Validate Parameters
IF @skip_pre_backup IS NULL
	SET	@skip_pre_backup = 0

--- Define the name of this program (for logs)
SELECT		@progname = 'spRest_ByBackupSetID'

--- Convert '' to NULL for @destination_db
IF LTRIM(RTRIM(@destination_db)) = ''
	SET	@destination_db = NULL

--- Destination Database
SELECT	@database = ISNULL(@destination_db, @database_name)

--- Determine if the user is authorized to do restores
EXEC [dbo].[spRest_IsAllowed] @database_name = @database, @is_allowed = @has_rights OUTPUT
IF @has_rights = 0 AND ISNULL(@restore_now, 0) = 1
  BEGIN
	SELECT	@sError = 'You (' + ORIGINAL_LOGIN() + ') are not authorized to restore the database ' + @database_name
	RAISERROR (@sError, 16, 1)
	RETURN
  END

--- Don't allow restoring system databases or centraldba_db with this process
IF @database IN ('master', 'model', 'msdb', DB_NAME())
  BEGIN
	SELECT	@sError = 'This process cannot be used to restore the database ' + @database
	RAISERROR (@sError, 16, 1)
  END

--- Error if no backups with this label exist
IF NOT EXISTS (SELECT TOP 1 * FROM [msdb].[dbo].[backupset] WHERE database_name = @database_name AND backup_set_id <= @last_backup_set_id)
  BEGIN
	SELECT	@sError = 'The requested backup of ' + QUOTENAME(@database_name) + ' was not found with the id ' + QUOTENAME(CAST(@last_backup_set_id as varchar(100)))
	RAISERROR (@sError, 16, 1)
  END

--- Convert '' to NULL for @destination_db
IF LTRIM(RTRIM(@destination_db)) = ''
	SET	@destination_db = NULL

--- Define Temp Tables
DECLARE @scripts TABLE
(
	id							int					PRIMARY KEY,
	name						sysname,
	command						nvarchar(3000),
	file_exists					bit,
	sResult						sysname,
	bStarted					bit,
	bSucceeded					bit,
	bProcessing					bit,
	sStatus						sysname
)

--- Get the Restore Scripts
INSERT INTO @scripts (id, name, command, file_exists, sResult, bStarted, bSucceeded, bProcessing, sStatus)
	EXEC	[dbo].[spRest_GetRestoreScripts]	@database_name = @database_name,
												@last_backup_set_id	= @last_backup_set_id,
												@destination_db = @destination_db,
												@stopat_time = @stopat_time,
												@skip_pre_backup = @skip_pre_backup
		

--- Return a resultset of the files needed to restore
SELECT		id,
			name,
			command,
			file_exists
FROM		@scripts
ORDER BY	id

--- No backups have been taken
IF NOT EXISTS (SELECT TOP 1 * FROM @scripts)
  BEGIN
	--- Raise the error
	RAISERROR ('There is no backup history recorded to restore to the backupset.  The backup file location may need to be inventoried.', 16, 1)
  END

--- At least one backup file could not be found on disk
ELSE IF EXISTS (SELECT TOP 1 * FROM @scripts WHERE file_exists = 0)
  BEGIN
	--- Raise the error
	RAISERROR ('Not all files needed for restore have been found on disk.', 16, 1)
  END

ELSE IF @restore_now = 1
  BEGIN
	--- Process the Restore Scripts
	DECLARE crsRestore CURSOR FOR 
		SELECT		name,
					command
		FROM		@scripts
		ORDER BY	id ASC
	OPEN crsRestore

	--- Get the first restore command and begin the restore process
	FETCH NEXT FROM crsRestore INTO @name, @sCommand
	WHILE @@FETCH_STATUS <> -1
		BEGIN
		BEGIN TRY
			--- Execute the next restore command
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @name
			EXEC	(@sCommand)
		END TRY

		--- If a command errors, exit the restore loop
		BEGIN CATCH
			--- Log the error - if possible
			SELECT @sResults = 'The database [' + @database + '] failed to process the step (' + @name + ') with the restore command (' + @sCommand + ').' 
			IF ERROR_NUMBER() IS NOT NULL
				SELECT @sResults = @sResults + CHAR(13)
								+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
								+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
								+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
								+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

			--- Print the error message to sysout
			PRINT @sResults

			--- Insert the error in the database
			INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
				VALUES (@database, GETDATE(), 'restore', 0, @sResults)

			--- Reraise the error to the caller
			RAISERROR (@sResults, 16, 1)

			BREAK
		END CATCH

		--- Move to the next restore command
		FETCH NEXT FROM crsRestore INTO @name, @sCommand
		END
	CLOSE crsRestore
	DEALLOCATE crsRestore

	--- Log the successful restore
	SELECT @sResults = 'The database [' + @database + '] was restored to the backup_set_id (' + CAST(@last_backup_set_id as varchar(100)) + ').' 
	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sResults
	INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
		VALUES (@database, GETDATE(), 'restore', 1, @sResults)
  END

SET NOCOUNT OFF